﻿using INTEX.DataBase.Tools;
using ReflectionStudio.Core.Events;
using System;
using System.Data;
using System.Linq;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public partial class ITXEXRPT
    {
        public void TSTVK02(DataSet db)
        {
            int x = 4, y = 0;
            string VKPPOSTYP = "";
            DataTable dtOutput = new DataTable();
            try
            {
                 
                #region======Title===========
               //这里设置抬头和输出的列
                //exAgent.SetCellValue(x, y + 2, "客户"); dtOutput.Columns.Add("KDKUNDNA", typeof(System.String));
                //exAgent.SetCellValue(x, y + , "联系人"); dtOutput.Columns.Add("VKKANSPRECH", typeof(System.String));
                //exAgent.SetCellValue(x, y + 7, "供应商"); dtOutput.Columns.Add("SUPPLIER", typeof(System.String));
                //exAgent.SetCellValue(x, y + 8, "采购单号"); dtOutput.Columns.Add("POORDER", typeof(System.String));
                //exAgent.SetCellValue(x, y + 9, "季节"); dtOutput.Columns.Add("VKPKOLL", typeof(System.String));

                //
                //exAgent.SetCellValue(x, y + 13, "预留"); dtOutput.Columns.Add("VKPRESERV", typeof(System.String));
                //exAgent.SetCellValue(x, y + 14, "留位"); dtOutput.Columns.Add("VKPZUGET", typeof(System.String));
                //exAgent.SetCellValue(x, y + 15, "确认"); dtOutput.Columns.Add("VKPFREIGE", typeof(System.String));
                //exAgent.SetCellValue(x, y + 16, "发货"); dtOutput.Columns.Add("VKPVERS", typeof(System.String));
                //exAgent.SetCellValue(x, y + 17, "开票"); dtOutput.Columns.Add("VKPFAKT", typeof(System.String));
                //exAgent.SetCellValue(x, y + 18, "交期"); dtOutput.Columns.Add("VKPLIEFTERM", typeof(System.String));
                //exAgent.SetCellValue(x, y + 19, "最后出货日期"); dtOutput.Columns.Add("DELDAT", typeof(System.String));
                //exAgent.SetCellValue(x, y + 23, "折扣"); dtOutput.Columns.Add("DIS", typeof(System.String));
                //exAgent.SetCellValue(x, y + 25, "付款方式"); dtOutput.Columns.Add("VKKZAHLBED", typeof(System.String));
                //exAgent.SetCellValue(x, y + 26, "贸易方式"); dtOutput.Columns.Add("VKKLIEFBED", typeof(System.String));
                //exAgent.SetCellValue(x, y + 27, "运输方式"); dtOutput.Columns.Add("VKKVERSART", typeof(System.String));
                //exAgent.SetCellValue(x, y + 28, "承运人"); dtOutput.Columns.Add("VKKSPED", typeof(System.String));
                //exAgent.SetCellValue(x, y + 29, "税"); dtOutput.Columns.Add("VKKVG", typeof(System.String));
                //exAgent.SetCellValue(x, y + 30, "成份"); dtOutput.Columns.Add("VAAFILLER", typeof(System.String));
                //exAgent.SetCellValue(x, y + 31, "客人色号"); dtOutput.Columns.Add("VKPKDFARBNR", typeof(System.String));
                //exAgent.SetCellValue(x, y + 32, "合同号"); dtOutput.Columns.Add("VKKBESTNR", typeof(System.String));
                //exAgent.SetCellValue(x, y + 33, "产品组"); dtOutput.Columns.Add("VAAPRGR", typeof(System.String));
                //exAgent.SetCellValue(x, y + 34, "产品组"); dtOutput.Columns.Add("VAAARTGRP", typeof(System.String));
                //exAgent.SetCellValue(x, y + 35, "创建日期"); dtOutput.Columns.Add("VKPNEUDAT", typeof(System.String));
                //exAgent.SetCellValue(x, y + 36, "跟单"); dtOutput.Columns.Add("VKPNEUBEN", typeof(System.String));
                //exAgent.SetCellValue(x, y + 37, "负责人"); dtOutput.Columns.Add("VKPSACHBE", typeof(System.String));
                //exAgent.SetCellValue(x, y + 5, "样布"); dtOutput.Columns.Add("SUR1", typeof(System.String));
                exAgent.SetCellValue(x, y + 1, "序号"); dtOutput.Columns.Add("", typeof(System.String));
                exAgent.SetCellValue(x, y + 2, ""); dtOutput.Columns.Add("VKPAUFKRS", typeof(System.String));
                exAgent.SetCellValue(x, y + 3, "销售单"); dtOutput.Columns.Add("VKPAUFLFD", typeof(System.String));
                exAgent.SetCellValue(x, y + 4, ""); dtOutput.Columns.Add("VKPAUFPOS", typeof(System.String));
                exAgent.SetCellValue(x, y + 5, "面料"); dtOutput.Columns.Add("VKPART", typeof(System.String));
                exAgent.SetCellValue(x, y + 6, "色号"); dtOutput.Columns.Add("VKPFARBE", typeof(System.String));
                exAgent.SetCellValue(x, y + 7, "单价"); dtOutput.Columns.Add("VKPPREIS", typeof(System.String));
                exAgent.SetCellValue(x, y + 8, "货币"); dtOutput.Columns.Add("VKKHWAEHRSL", typeof(System.String));
                exAgent.SetCellValue(x, y + 9, "数量"); dtOutput.Columns.Add("VKPMENGE1", typeof(System.String));
                exAgent.SetCellValue(x, y + 10, "小计"); dtOutput.Columns.Add("SUR", typeof(System.String));
                exAgent.SetCellValue(x, y + 11, "样布附加费"); dtOutput.Columns.Add("SUR1", typeof(System.String));
                exAgent.SetCellValue(x, y + 12, "运费"); dtOutput.Columns.Add("SUR2", typeof(System.String));
                exAgent.SetCellValue(x, y + 13, "总金额"); dtOutput.Columns.Add("TOTVALUE", typeof(System.String));
                
                #endregion               

                #region======ConvertData===
                #endregion
                //var qGW = from GW in db.Tables["VKPOS"].AsEnumerable()
                //          group GW by new
                //          {
                //              PT = GW["VKPPOSTYP"].ToString(),
                //          }
                //              into g
                //              select new
                //              {
                //                  KEY = g.Key,
                //              };
                //上面是若需要对列表分组输出，现在本身屏蔽了。
                //foreach (var GW in qGW)
                //{
                    //VKPPOSTYP = GW.KEY.PT;
                    //exAgent.RenameSheet(date);
                DataRow[] rows = db.Tables["VKPOS"].Select("VKPSA='  '");               
                DataTable dtSoruce = rows.CopyToDataTable();
                DataRow rowNum = rows[0];
                //DataTable dtSoruce = db.Tables["VKPOS"];
               
                    dtOutput.Clear();
                    dtOutput.Merge(dtSoruce, false, MissingSchemaAction.Ignore);
                    foreach (DataRow rx in dtOutput.Rows)
                    {//这里处理需要进行的数字和中文
                        //rx["KDKUNDNA"] = Helper.GetGB2312Value(rx["KDKUNDNA"].ToString());
                        //rx["VKKANSPRECH"] = Helper.GetGB2312Value(rx["VKKANSPRECH"].ToString());
                        //rx["SUPPLIER"] = Helper.GetGB2312Value(rx["SUPPLIER"].ToString());
                        rx["VKPFARBE"] = Helper.GetGB2312Value(rx["VKPFARBE"].ToString());
                        rx["VKPMENGE1"] = (Convert.ToInt32(rx["VKPMENGE1"]) * 0.01).ToString().Trim();
                        //rx["VKPRESERV"] = (Convert.ToInt32(rx["VKPRESERV"]) * 0.01).ToString().Trim();
                        //rx["VKPZUGET"] = (Convert.ToInt32(rx["VKPZUGET"]) * 0.01).ToString().Trim();
                        //rx["VKPFREIGE"] = (Convert.ToInt32(rx["VKPFREIGE"]) * 0.01).ToString().Trim();
                        //rx["VKPVERS"] = (Convert.ToInt32(rx["VKPVERS"]) * 0.01).ToString().Trim();
                        //rx["TOTVALUE"] = (Convert.ToInt32(rx["TOTVALUE"]) * 0.001).ToString().Trim();
                        rx["VKPPREIS"] = (Convert.ToInt32(rx["VKPPREIS"]) * 0.001).ToString().Trim();
                        rx["SUR"] = ((Convert.ToDouble(rx["VKPPREIS"])) * (Convert.ToDouble(rx["VKPMENGE1"]))).ToString();
                        rx["TOTVALUE"] = (Convert.ToDouble(rx["SUR"].ToString()) + Convert.ToDouble(rx["SUR1"].ToString()) + Convert.ToDouble(rx["SUR2"].ToString())).ToString();
                    }
                    #region====Excel==========
                    int count = dtOutput.Rows.Count;
                    //exAgent.RangeCopy(4, 50, 5, 4 + count);
                    exAgent.DataTableToExcel(dtOutput, x, 1);
                    for (int i = 0; i < count; i++)
                    {
                        exAgent.SetCellValue(i + 4, 1, i+1);
                    }

                    //MergeSame(x, count);
                    //exAgent.SetCellValue(1, 1, 1);
                    exAgent.BatchConvert2Number(4, 7, count + 3, 13);
                    //exAgent.BatchConvert2Number(4, 5, count + 3, 5);
                    //exAgent.BatchConvert2Number(4, 16, count + 3, 16);
                    exAgent.SetCellValue(1, 1, Helper.GetGB2312Value(rowNum["KDKUNDNA"].ToString()).Trim() + rowNum["VKPKOLL"].ToString().Trim() + "对账单");
                    exAgent.SetCellValue(count + 5, 11, "上海缇素进出口有限公司");
                    exAgent.SetCellValue(count + 6, 11,Helper.GetGB2312Value(rowNum["EXCELFILE"].ToString()).Trim());
                    exAgent.SetCellValue(count + 7, 11, date.Substring(0,4).ToString()+"-"+date.Substring(4,2).ToString()+"-"+date.Substring(6,2).ToString());                  
                    if (rowNum["ACCOUNT"].ToString().Trim() == "STD")
                    {
                        exAgent.SetCellValue(count+5, 1, "以上价格为含税到仓价格，相关样布费用请付款至：");
                        exAgent.SetCellValue(count + 6, 1, "开 户 名：上海缇素进出口有限公司");
                        exAgent.SetCellValue(count + 7, 1, "开户银行：农行上海分行普陀区长寿支行");
                        exAgent.SetCellValue(count + 8, 1, "账    号：03 3762 000400 12434");

                    }
                    else
                    {
                        exAgent.SetCellValue(count+5, 1, "以上价格为不含税到仓价格，相关样布费用请付款至：");
                        exAgent.SetCellValue(count + 6, 1, "开 户 名：章翊");
                        exAgent.SetCellValue(count + 7, 1, "开户银行：农行上海分行普陀区长寿支行");
                        exAgent.SetCellValue(count + 8, 1, "账    号：6228 4800 3038 9125 219");
                    }

                   
                    #endregion
                    //break;
                //}
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                EventDispatcher.Instance.RaiseStatus("完成", StatusEventType.StopProgress);
            }
        }
         
        private void TSTVK02x(DataSet db)  
        {
            try
            {
                DataTable tbEKKOPF = dsSource.Tables["VKPOS"];                
                for (int i = 0; i < tbEKKOPF.Rows.Count; i++)
                {
                    DataRow TEMP=tbEKKOPF.Rows[i];
                    string VKPSA = "";
                    VKPSA = TEMP["VKPSA"].ToString().Trim(); exAgent.SetCellValue(i + 3, 1, VKPSA);
                    string KDKUNDNA = "";//中文
                    KDKUNDNA = TEMP["KDKUNDNA"].ToString().Trim(); exAgent.SetCellValue(i + 3, 2, Helper.GetGB2312Value(KDKUNDNA).ToString().Trim());
                    string VKKANSPRECH = "";//中文
                    VKKANSPRECH = TEMP["VKKANSPRECH"].ToString().Trim(); exAgent.SetCellValue(i + 3, 3, Helper.GetGB2312Value(VKKANSPRECH).ToString().Trim());
                    string VKPAUFKRS = "";
                    VKPAUFKRS = TEMP["VKPAUFKRS"].ToString().Trim(); exAgent.SetCellValue(i + 3, 4, VKPAUFKRS);
                    string VKPAUFLFD = "";
                    VKPAUFLFD = TEMP["VKPAUFLFD"].ToString().Trim(); exAgent.SetCellValue(i + 3, 5, VKPAUFLFD);
                    string VKPAUFPOS = "";//pumn
                    VKPAUFPOS = TEMP["VKPAUFPOS"].ToString().Trim(); exAgent.SetCellValue(i + 3, 6, VKPAUFPOS);
                    string SUPPLIER = "";//中文
                    SUPPLIER = TEMP["SUPPLIER"].ToString().Trim(); exAgent.SetCellValue(i + 3, 7,Helper.GetGB2312Value(SUPPLIER).ToString().Trim());
                    string POORDER = "";
                    POORDER = TEMP["POORDER"].ToString().Trim(); exAgent.SetCellValue(i + 3, 8,Helper.GetGB2312Value(POORDER).ToString().Trim());
                    string VKPKOLL = "";//中文
                    VKPKOLL = TEMP["VKPKOLL"].ToString().Trim(); exAgent.SetCellValue(i + 3, 9,VKPKOLL);
                    string VKPART = "";
                    VKPART = TEMP["VKPART"].ToString().Trim(); exAgent.SetCellValue(i + 3, 10, VKPART);
                    string VKPFARBE = "";
                    VKPFARBE = TEMP["VKPFARBE"].ToString().Trim(); exAgent.SetCellValue(i + 3, 11, VKPFARBE);
                    string VKPMENGE1 = "";
                    VKPMENGE1 =(Convert.ToInt32(TEMP["VKPMENGE1"])*0.01).ToString().Trim(); exAgent.SetCellValue(i + 3, 12, VKPMENGE1);
                    string VKPRESERV = "";//D100
                    VKPRESERV = (Convert.ToInt32(TEMP["VKPRESERV"]) * 0.01).ToString().Trim(); exAgent.SetCellValue(i + 3, 13, VKPRESERV);
                    string VKPZUGET = "";//D100
                    VKPZUGET = (Convert.ToInt32(TEMP["VKPZUGET"]) * 0.01).ToString().Trim(); exAgent.SetCellValue(i + 3, 14, VKPZUGET);
                    string VKPFREIGE = "";
                    VKPFREIGE = (Convert.ToInt32(TEMP["VKPFREIGE"]) * 0.01).ToString().Trim(); exAgent.SetCellValue(i + 3, 15, VKPFREIGE);
                    string VKPVERS = "";
                    VKPVERS = (Convert.ToInt32(TEMP["VKPVERS"]) * 0.01).ToString().Trim(); exAgent.SetCellValue(i + 3, 16, VKPVERS);
                    string VKPFAKT = "";//price
                    VKPFAKT = (Convert.ToInt32(TEMP["VKPFAKT"]) * 0.01).ToString().Trim(); exAgent.SetCellValue(i + 3, 17, VKPFAKT);
                    string VKPLIEFTERM = "";
                    VKPLIEFTERM = TEMP["VKPLIEFTERM"].ToString().Trim(); exAgent.SetCellValue(i + 3, 18, VKPLIEFTERM);
                    string DELDAT = "";
                    DELDAT = TEMP["DELDAT"].ToString().Trim(); exAgent.SetCellValue(i + 3, 19, DELDAT);
                    string VKPPREIS = "";
                    VKPPREIS = (Convert.ToInt32(TEMP["VKPPREIS"]) * 0.001).ToString().Trim(); exAgent.SetCellValue(i + 3, 20, VKPPREIS);
                    string TOTVALUE = "";
                    TOTVALUE = TEMP["TOTVALUE"].ToString().Trim(); exAgent.SetCellValue(i + 3, 21, TOTVALUE);
                    string SUR = "";
                    SUR = TEMP["SUR"].ToString().Trim(); exAgent.SetCellValue(i + 3, 22, SUR);
                    string DIS = "";
                    DIS = TEMP["DIS"].ToString().Trim(); exAgent.SetCellValue(i + 3, 23, DIS);
                    string VKKHWAEHRSL = "";
                    VKKHWAEHRSL = TEMP["VKKHWAEHRSL"].ToString().Trim(); exAgent.SetCellValue(i + 3, 24, VKKHWAEHRSL);
                    string VKKZAHLBED = "";
                    VKKZAHLBED = TEMP["VKKZAHLBED"].ToString().Trim(); exAgent.SetCellValue(i + 3, 25, VKKZAHLBED);
                    string VKKLIEFBED = "";
                    VKKLIEFBED = TEMP["VKKLIEFBED"].ToString().Trim(); exAgent.SetCellValue(i + 3, 26, VKKLIEFBED);
                    string VKKVERSART = "";
                    VKKVERSART = TEMP["VKKVERSART"].ToString().Trim(); exAgent.SetCellValue(i + 3, 27, VKKVERSART);
                    string VKKSPED = "";
                    VKKSPED = TEMP["VKKSPED"].ToString().Trim(); exAgent.SetCellValue(i + 3, 28, VKKSPED);
                    string VKKVG = "";
                    VKKVG = TEMP["VKKVG"].ToString().Trim(); exAgent.SetCellValue(i + 3, 29, VKKVG);
                    string VAAFILLER = "";
                    VAAFILLER = TEMP["VAAFILLER"].ToString().Trim(); exAgent.SetCellValue(i + 3, 30, VAAFILLER);
                    string VKPKDFARBNR = "";
                    VKPKDFARBNR = TEMP["VKPKDFARBNR"].ToString().Trim(); exAgent.SetCellValue(i + 3, 31, VKPKDFARBNR);
                    string VKKBESTNR = "";
                    VKKBESTNR = TEMP["VKKBESTNR"].ToString().Trim(); exAgent.SetCellValue(i + 3, 32, VKKBESTNR);
                    string VAAPRGR = "";
                    VAAPRGR = TEMP["VAAPRGR"].ToString().Trim(); exAgent.SetCellValue(i + 3, 33, VAAPRGR);
                    string VAAARTGRP = "";
                    VAAARTGRP = TEMP["VAAARTGRP"].ToString().Trim(); exAgent.SetCellValue(i + 3, 34, VAAARTGRP);
                    string VKPNEUDAT = "";
                    VKPNEUDAT = TEMP["VKPNEUDAT"].ToString().Trim(); exAgent.SetCellValue(i + 3, 35, VKPNEUDAT);
                    string VKPNEUBEN = "";
                    VKPNEUBEN = TEMP["VKPNEUBEN"].ToString().Trim(); exAgent.SetCellValue(i + 3, 36, VKPNEUBEN);
                    string VKPSACHBE = "";
                    VKPSACHBE = TEMP["VKPSACHBE"].ToString().Trim(); exAgent.SetCellValue(i + 3, 37, VKPSACHBE);
                   
                    
                }
                //DataRow dwEKKOPF = tbEKKOPF.Rows[0];
                //string src = "",CURRENCY = "";

                //src = dwEKKOPF["ORDNO"].ToString();
                //exAgent.SetCellValue(4, 34, src);
                //src = dwEKKOPF["CUSTOMER"].ToString().Trim();
                //exAgent.SetCellValue(6, 5, Helper.GetGB2312Value(src));
                //src = dwEKKOPF["SUPP"].ToString().Trim();
                //exAgent.SetCellValue(6, 19, Helper.GetGB2312Value(src));
                //src = dwEKKOPF["DAT"].ToString().Trim();
                //exAgent.SetCellValue(6, 34, src);
                //src = Helper.GetGB2312Value(dwEKKOPF["INVNAME"].ToString().Trim()) + System.Environment.NewLine + Helper.GetGB2312Value(dwEKKOPF["INVADR"].ToString().Trim());
                //exAgent.SetCellValue(9, 1, src);
                //src = Helper.GetGB2312Value(dwEKKOPF["DELNAME"].ToString().Trim()) + System.Environment.NewLine + Helper.GetGB2312Value(dwEKKOPF["DELADR"].ToString().Trim());
                //exAgent.SetCellValue(9, 20, src);
                //src = dwEKKOPF["DELT"].ToString().Trim();
                //exAgent.SetCellValue(172, 7, Helper.GetGB2312Value(src));
                //src = dwEKKOPF["PAYBEZ"].ToString().Trim();
                //exAgent.SetCellValue(171, 7, Helper.GetGB2312Value(src));
                //src = dwEKKOPF["TRU"].ToString().Trim();
                //exAgent.SetCellValue(172, 25, Helper.GetGB2312Value(src));
                //src = dwEKKOPF["TOLERANCE"].ToString().Trim();
                //exAgent.SetCellValue(182, 12, Helper.GetGB2312Value(src));
                //src = dwEKKOPF["REMARK"].ToString().Trim();
                //exAgent.SetCellValue(183, 12, Helper.GetGB2312Value(src));
                //CURRENCY = dwEKKOPF["CURRENCY"].ToString().Trim();
                //exAgent.SetCellValue(13, 29, "Total value (" + Helper.GetGB2312Value(CURRENCY) + ")");

                //DataTable tbEKPOS = dsSource.Tables["EKPOS"];
                //int x = 14;
                //string wid = "", totwid = "", gm = "", sqm = "",comp ="";
                //foreach (DataRow dwEKPOS in tbEKPOS.Rows)
                //{
                //    src = dwEKPOS["POS"].ToString().Trim();
                //    exAgent.SetCellValue(x, 1, System.Convert.ToInt64(src));
                //    src = dwEKPOS["ART"].ToString().Trim() + System.Environment.NewLine + dwEKPOS["ARTDES"].ToString().Trim();
                //    exAgent.SetCellValue(x, 3, Helper.GetGB2312Value(src));
                //    src = dwEKPOS["COLOR"].ToString().Trim();
                //    exAgent.SetCellValue(x, 11, Helper.GetGB2312Value(src));
                //    src = dwEKPOS["QTY"].ToString().Trim();
                //    exAgent.SetCellValue(x, 15, System.Convert.ToDouble(src));
                //    src = dwEKPOS["UNIT"].ToString().Trim();
                //    exAgent.SetCellValue(x, 18, Helper.GetGB2312Value(src));
                //    exAgent.SetCellValue(x, 20, dwEKPOS["PRICE"].ToString().Trim() + " " + Helper.GetGB2312Value(CURRENCY).ToLower());
                //    src = dwEKPOS["SUR"].ToString().Trim();
                //    exAgent.SetCellValue(x, 23, System.Convert.ToDouble(src));
                //    src = dwEKPOS["DIS"].ToString().Trim();
                //    exAgent.SetCellValue(x, 26, System.Convert.ToDouble(src));
                //    src = dwEKPOS["SDAT"].ToString().Trim();
                //    exAgent.SetCellValue(x, 33, src);
                //    src = dwEKPOS["BDAT"].ToString().Trim();
                //    exAgent.SetCellValue(x, 37, src);
                //    src = dwEKPOS["VALUE"].ToString().Trim();
                //    exAgent.SetCellValue(x, 29, System.Convert.ToDouble(src));
                    
                //    wid = dwEKPOS["WID"].ToString().Trim();
                //    totwid = dwEKPOS["TOTWID"].ToString().Trim();
                //    gm = dwEKPOS["GM"].ToString().Trim();
                //    sqm = dwEKPOS["SQM"].ToString().Trim();
                //    comp = Helper.GetGB2312Value(dwEKPOS["COMP"].ToString().Trim());
                //    src = "Composition:" + comp; 
                //    exAgent.SetCellValue(x+1, 3, src);
                //    src = "";
                //    if (totwid != "0")
                //        {
                //            src = src + "Total width:" + totwid + "cm  ";
                //        }
                //        if (wid != "0")
                //        {
                //            src = src + "Cuttable:" + wid + "cm     ";
                //        }
                //        if (gm != "0")
                //        {
                //            src = src + "Weigth:" + gm + "g/m  " + sqm + "g/sqm ";
                //        }                    
                //    exAgent.SetCellValue(x+2, 3, src);
                //    x = x + 3;

                //}
                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);

            }            
        }
               
    }
}
